import sys
import os
from PyQt5.QtWidgets import (QApplication, QMainWindow, QPushButton, QLabel, 
                            QVBoxLayout, QHBoxLayout, QFileDialog, QWidget, 
                            QProgressBar, QMessageBox)
from PyQt5.QtCore import Qt, QThread, pyqtSignal
from PyQt5.QtGui import QFont


# 你的原始数据分析脚本代码（保持不变）
def run_analysis(input_file, output_file):
    # 这里是你的原始脚本代码
    # 为了保持代码清晰，我将它作为一个函数调用
    # 实际使用时，你可以直接将你的原始代码粘贴到这里
    
    # 导入原始脚本中使用的库
    from openpyxl import load_workbook
    from openpyxl import Workbook
    
    # 读取 Excel 文件
    try:
        table = load_workbook(filename=input_file)
        sheet = table['Sheet1']
    except Exception as e:
        return False, f"读取Excel文件时出错: {str(e)}"
    
    data_origin = []
    
    # 提取数据
    for i, row in enumerate(sheet.iter_rows(values_only=True)):
        if i == 0:  # 第一行（日期）
            dates = [cell for cell in row[1::2]]  # 直接提取所有日期
            data_origin.append(row)
        elif i == 1:  # 第二行（指标类型）
            metrics = [cell for cell in row[1:]]  # 直接提取所有指标
            data_origin.append(row)
        else:  # 数据行
            data_origin.append(list(row))
    
    # 初始化字典
    result = {}
    print(dates)
    # 遍历数据行（从第 3 行开始）
    for row in data_origin[2:]:
        event_name = row[0]
        values = row[1:]  # 所有数值
        
        # 遍历所有指标
        for i in range(len(metrics)):
            date_idx = i // 2  # 每 2 个指标对应 1 个日期
            if date_idx >= len(dates):
                break  # 防止越界
            
            date = dates[date_idx]
            metric_type = metrics[i]
            
            key = f"{date}_{metric_type}"
            
            if key not in result:
                result[key] = {}
            
            result[key][event_name] = values[i]
    
    # 用户行为计算,日期传参
    def safe_get(d, keys, default=0):
        """安全获取多级字典的值，找不到返回默认值"""
        for key in keys:
            if isinstance(d, dict) and key in d:
                d = d[key]
            else:
                return default
        return d

    def safe_division(numerator, denominator, default=0):
        try:
            if denominator == 0 or denominator is None or denominator == '':
                return default
            return round(numerator / denominator, 4)
        except Exception:
            return default

    def calculate_user(date):
        content = []
        content.append(date)
        # 页面深度计算,正确
        page_depth = safe_division(safe_get(result, [f'{date}_事件数', 'page_view'], 0), safe_get(result, [f'{date}_用户总数', 'page_view'], 0))
        content.append(page_depth)
        # 落地页深度计算，正确
        detail_depth = safe_division(safe_get(result, [f'{date}_事件数', 'app_detail_view'], 0), safe_get(result, [f'{date}_用户总数', 'app_detail_view'], 0))
        content.append(detail_depth)
        try:
            # 落地页人均访问数，正确
            detail_view_rate_issues = safe_division(safe_get(result, [f'{date}_事件数', 'promo_view'], 0), safe_get(result, [f'{date}_用户总数', 'promo_view'], 0))
            content.append(detail_view_rate_issues)
        except Exception as e:
            detail_view_rate_issues = 0
            content.append(detail_view_rate_issues)
            print(f"ValueErro:{e}")
        # 下载页深度,正确
        download_detail_issues = safe_division(safe_get(result, [f'{date}_用户总数', 'app_download_view'], 0), safe_get(result, [f'{date}_用户总数', 'page_view'], 0))
        content.append(download_detail_issues)
        # 滑动行为深度，正确
        scroll_issues = safe_division(safe_get(result, [f'{date}_用户总数', 'custom_scroll'], 0), safe_get(result, [f'{date}_用户总数', 'page_view'], 0))
        content.append(scroll_issues)
        # 点击应用跳转站外深度，正确
        store_click_issues = safe_division(safe_get(result, [f'{date}_用户总数', 'click_storebutton'], 0), safe_get(result, [f'{date}_用户总数', 'page_view'], 0))
        content.append(store_click_issues)
        # 详情页滑动按钮点击，正确
        getapp_click_issues = safe_division(safe_get(result, [f'{date}_事件数', 'click_getapp'], 0), safe_get(result, [f'{date}_事件数', 'app_detail_view'], 0))
        content.append(getapp_click_issues)
        # 详情页下载按钮点击，正确
        download_click_issues = safe_division(safe_get(result, [f'{date}_事件数', 'click_download'], 0), safe_get(result, [f'{date}_事件数', 'app_detail_view'], 0))
        content.append(download_click_issues)
        return content
    
    # ads广告行为计算,日期传参
    def calculate_ads(date):
        content = []
        content.append(date)
        per_ads = safe_division(safe_get(result, [f'{date}_事件数', 'ads_show'], 0), safe_get(result, [f'{date}_用户总数', 'ads_show'], 0))
        try:
            per_page_ads = safe_division(safe_get(result, [f'{date}_事件数', 'ads_show'], 0), safe_get(result, [f'{date}_事件数', 'page_view'], 0))
        except Exception as e:
            per_page_ads = 0
            print(f"ValueErro:{e}")
        per_ads_show_issues = safe_division(safe_get(result, [f'{date}_用户总数', 'ads_show'], 0), safe_get(result, [f'{date}_用户总数', 'page_view'], 0))
        five_ads_view_people = safe_division(safe_get(result, [f'{date}_用户总数', 'ads_show5'], 0), safe_get(result, [f'{date}_用户总数', 'page_view'], 0))
        three_ads_view_people = safe_division(safe_get(result, [f'{date}_用户总数', 'ads_inpage_show3'], 0), safe_get(result, [f'{date}_用户总数', 'page_view'], 0))
        per_ads_show_inpage_issues = safe_division(safe_get(result, [f'{date}_事件数', 'ads_show_inpage'], 0), safe_get(result, [f'{date}_事件数', 'page_view'], 0))
        per_anchor_show_issues = safe_division(safe_get(result, [f'{date}_事件数', 'ads_show_anchor'], 0), safe_get(result, [f'{date}_事件数', 'page_view'], 0))
        per_vignette_show_issues = safe_division(safe_get(result, [f'{date}_事件数', 'ads_show_vignette'], 0), safe_get(result, [f'{date}_事件数', 'page_view'], 0))
        request_rate = safe_division(safe_get(result, [f'{date}_事件数', 'ads_request'], 0), safe_get(result, [f'{date}_事件数', 'page_view'], 0))
        try:
            ads_unfill_rate = safe_division(safe_get(result, [f'{date}_事件数', 'ads_unfilled'], 0), safe_get(result, [f'{date}_事件数', 'ads_request'], 0))
        except Exception as e:
            ads_unfill_rate = 0
            print(f"ValueErro:{e}")
        effective_show_rate = safe_division(safe_get(result, [f'{date}_事件数', 'ads_show'], 0), safe_get(result, [f'{date}_事件数', 'ads_request'], 0))
        ads_click_rate = safe_division(safe_get(result, [f'{date}_事件数', 'ads_click'], 0), safe_get(result, [f'{date}_事件数', 'ads_show'], 0))
        content.extend([per_ads,per_page_ads,per_ads_show_issues,five_ads_view_people,three_ads_view_people,per_ads_show_inpage_issues,per_anchor_show_issues,per_vignette_show_issues,request_rate,ads_unfill_rate,effective_show_rate,ads_click_rate])
        return content
    
    # adx广告行为计算,日期传参
    def calculate_adx(date):
        content = []
        content.append(date)
        try:
            per_adx = safe_division(safe_get(result, [f'{date}_事件数', 'adx_show'], 0), safe_get(result, [f'{date}_用户总数', 'adx_show'], 0))
        except Exception as e:
            per_adx = 0
            print(f"ValueErro:{e}")
        try:
            per_page_adx = safe_division(safe_get(result, [f'{date}_事件数', 'adx_show'], 0), safe_get(result, [f'{date}_事件数', 'page_view'], 0))
        except Exception as e:
            per_page_adx = 0
            print(f"ValueErro:{e}")
        try:
            per_adx_show_issues = safe_division(safe_get(result, [f'{date}_用户总数', 'adx_show'], 0), safe_get(result, [f'{date}_用户总数', 'page_view'], 0))
        except Exception as e:
            per_adx_show_issues = 0
            print(f"ValueErro:{e}")
        try:
            five_adx_view_people = safe_division(safe_get(result, [f'{date}_用户总数', 'adx_show5'], 0), safe_get(result, [f'{date}_用户总数', 'page_view'], 0))
        except Exception as e:
            five_adx_view_people = 0
            print(f"ValueErro:{e}")
        try:
            three_adx_view_people = safe_division(safe_get(result, [f'{date}_用户总数', 'adx_inpage_show3'], 0), safe_get(result, [f'{date}_用户总数', 'page_view'], 0))
        except Exception as e:
            three_adx_view_people = 0
            print(f"ValueErro:{e}")
        try:
            per_adx_show_inpage_issues = safe_division(safe_get(result, [f'{date}_事件数', 'adx_show_inpage'], 0), safe_get(result, [f'{date}_事件数', 'page_view'], 0))
        except Exception as e:
            per_adx_show_inpage_issues = 0
            print(f"ValueErro:{e}")
        try:
            per_anchor_show_issues = safe_division(safe_get(result, [f'{date}_事件数', 'adx_show_anchor'], 0), safe_get(result, [f'{date}_事件数', 'page_view'], 0))
        except Exception as e:
            per_anchor_show_issues = 0
            print(f"ValueErro:{e}")
        try:
            per_vignette_show_issues = safe_division(safe_get(result, [f'{date}_事件数', 'adx_show_vignette'], 0), safe_get(result, [f'{date}_事件数', 'page_view'], 0))
        except Exception as e:
            per_vignette_show_issues = 0
            print(f"ValueErro:{e}")
        try:
            request_rate = safe_division(safe_get(result, [f'{date}_事件数', 'adx_request'], 0), safe_get(result, [f'{date}_事件数', 'page_view'], 0))
        except Exception as e:
            request_rate = 0
            print(f"ValueErro:{e}")
        try:
            adx_unfill_rate = safe_division(safe_get(result, [f'{date}_事件数', 'adx_unfilled'], 0), safe_get(result, [f'{date}_事件数', 'adx_request'], 0))
        except Exception as e:
            adx_unfill_rate = 0
            print(f"ValueErro:{e}")
        try:
            effective_show_rate = safe_division(safe_get(result, [f'{date}_事件数', 'adx_show'], 0), safe_get(result, [f'{date}_事件数', 'adx_request'], 0))
        except Exception as e:
            effective_show_rate = 0
            print(f"ValueErro:{e}")
        try:
            adx_click_rate = safe_division(safe_get(result, [f'{date}_事件数', 'adx_click'], 0), safe_get(result, [f'{date}_事件数', 'adx_show'], 0))
        except Exception as e:
            adx_click_rate = 0
            print(f"ValueErro:{e}")
        content.extend([per_adx,per_page_adx,per_adx_show_issues,five_adx_view_people,three_adx_view_people,per_adx_show_inpage_issues,per_anchor_show_issues,per_vignette_show_issues,request_rate,adx_unfill_rate,effective_show_rate,adx_click_rate])
        return content


    try:
        print('dates:',dates)
        user_data = []
        for i in dates:
            content = calculate_user(i)
            user_data.append(content)
        print(f"user_data:{user_data}")
        ads_data = []
        for i in dates:
            content_ads = calculate_ads(i)
            ads_data.append(content_ads)
        print(f"ads_data:{ads_data}")
        adx_data = []
        for i in dates:
            content_adx = calculate_adx(i)
            adx_data.append(content_adx)
        print(f"user_data:{user_data}")
    except Exception as e:
        print(f"ERRO:{e}")

    # 定义存储表格的功能函数，有三种表头，三种数据
    def save_to_excel(data1, data2, data3, headers1, headers2, headers3, file_path):
        # 创建工作簿和工作表
        wb = Workbook()
        # 用户表存入数据
        ws1 = wb.active
        ws1.title = 'User_Behavior'
        # 添加表头
        ws1.append(headers1)
        # 添加数据
        for row in data1:
            ws1.append(row)
        # ads广告行为表存入数据
        ws2 = wb.create_sheet(title="Ads_Campaign")
        # 添加表头
        ws2.append(headers2)
        # 添加数据
        for row in data2:
            ws2.append(row)

        # adx广告行为表存入数据
        ws3 = wb.create_sheet(title='Adx_Campaign')
        ws3.append(headers3)
        for row in data3:
            ws3.append(row)

        # 保存文件
        wb.save(file_path)
    
    # 自定义表头
    headers_user = [
        'Date','人均页面访问数','人均详情页人均访问数','落地页人均访问数','下载页深度','滑动行为深度','点击应用跳转站外深度','详情页滑动按钮点击','详情页下载按钮点击'
    ]
    headers_ads = [
        'Date','人均广告次数','页均广告次数','触发广告人数占比','五广告展示人数占比','三页内广告展示人数占比','人均页内广告触发数','人均锚定广告触发数','人均穿插广告触发数','广告请求比','广告填充比','广告有效展示率','广告点击率'
    ]
    headers_adx = [
        'Date','人均广告次数','页均广告次数','触发广告人数占比','五广告展示人数占比','三页内广告展示人数占比','人均页内广告触发数','人均锚定广告触发数','人均穿插广告触发数','广告请求比','广告填充比','广告有效展示率','广告点击率'
    ]
    # 保存文件
    try:
        print('adx:',adx_data)
        save_to_excel(user_data, ads_data, adx_data, headers_user, headers_ads,headers_adx, output_file)
        return True, f"数据已成功保存到 {output_file}"
    except Exception as e:
        return False, f"保存Excel文件时出错: {str(e)}"


# 创建一个工作线程类，用于在后台运行数据分析
class AnalysisThread(QThread):
    progress_updated = pyqtSignal(int)
    analysis_completed = pyqtSignal(bool, str)
    
    def __init__(self, input_file, output_file):
        super().__init__()
        self.input_file = input_file
        self.output_file = output_file
        
    def run(self):
        # 模拟进度更新
        self.progress_updated.emit(10)
        
        # 运行数据分析
        success, message = run_analysis(self.input_file, self.output_file)
        
        # 完成进度
        self.progress_updated.emit(100)
        
        # 发送完成信号
        self.analysis_completed.emit(success, message)


# 创建GUI应用程序
class ExcelAnalyzerApp(QMainWindow):
    def __init__(self):
        super().__init__()
        
        # 设置窗口标题和大小
        self.setWindowTitle("Excel数据分析工具")
        self.setGeometry(100, 100, 800, 500)
        
        # 创建中心部件和布局
        self.central_widget = QWidget()
        self.setCentralWidget(self.central_widget)
        self.main_layout = QVBoxLayout(self.central_widget)
        
        # 创建标题标签
        title_label = QLabel("Excel数据分析工具")
        title_label.setAlignment(Qt.AlignCenter)
        title_label.setFont(QFont(".AppleSystemUIFont", 16, QFont.Bold))
        self.main_layout.addWidget(title_label)
        
        # 添加一些说明文字
        desc_label = QLabel("请选择要分析的Excel文件，程序将自动处理并生成结果文件。")
        desc_label.setAlignment(Qt.AlignCenter)
        desc_label.setWordWrap(True)
        desc_label.setStyleSheet("color: #666; margin: 10px 0;")
        self.main_layout.addWidget(desc_label)
        
        # 创建文件选择区域
        file_layout = QHBoxLayout()
        
        self.input_file_label = QLabel("未选择文件")
        self.input_file_label.setStyleSheet("border: 1px solid #ccc; padding: 5px; background-color: #f5f5f5;")
        self.input_file_label.setWordWrap(True)
        
        self.select_file_btn = QPushButton("选择文件")
        self.select_file_btn.setStyleSheet("background-color: #4CAF50; color: white; padding: 5px 15px;")
        self.select_file_btn.clicked.connect(self.select_input_file)
        
        file_layout.addWidget(self.input_file_label, 3)
        file_layout.addWidget(self.select_file_btn, 1)
        
        self.main_layout.addLayout(file_layout)
        
        # 创建输出文件区域
        output_layout = QHBoxLayout()
        
        self.output_file_label = QLabel("未设置输出文件")
        self.output_file_label.setStyleSheet("border: 1px solid #ccc; padding: 5px; background-color: #f5f5f5;")
        self.output_file_label.setWordWrap(True)
        
        self.select_output_btn = QPushButton("设置输出")
        self.select_output_btn.setStyleSheet("background-color: #2196F3; color: white; padding: 5px 15px;")
        self.select_output_btn.clicked.connect(self.select_output_file)
        
        output_layout.addWidget(self.output_file_label, 3)
        output_layout.addWidget(self.select_output_btn, 1)
        
        self.main_layout.addLayout(output_layout)
        
        # 添加进度条
        self.progress_bar = QProgressBar()
        self.progress_bar.setValue(0)
        self.progress_bar.setStyleSheet("QProgressBar {border: 1px solid grey; border-radius: 3px; text-align: center;}"
                                       "QProgressBar::chunk {background-color: #3498db;}")
        self.main_layout.addWidget(self.progress_bar)
        
        # 添加运行按钮
        self.run_btn = QPushButton("开始分析")
        self.run_btn.setStyleSheet("background-color: #2F3C53; color: white; padding: 10px 20px; font-size: 20px;")
        self.run_btn.setFixedHeight(40)
        self.run_btn.clicked.connect(self.run_analysis)
        self.main_layout.addWidget(self.run_btn)
        
        # 添加状态标签
        self.status_label = QLabel("就绪")
        self.status_label.setAlignment(Qt.AlignCenter)
        self.status_label.setStyleSheet("color: #666; margin-top: 10px;")
        self.main_layout.addWidget(self.status_label)
        
        # 添加一些间距
        self.main_layout.addStretch(1)
        
        # 初始化文件路径变量
        self.input_file_path = ""
        self.output_file_path = ""
        
        # 初始化分析线程
        self.analysis_thread = None
    
    def select_input_file(self):
        file_path, _ = QFileDialog.getOpenFileName(self, "选择Excel文件", "", "Excel Files (*.xlsx *.xls)")
        if file_path:
            self.input_file_path = file_path
            self.input_file_label.setText(os.path.basename(file_path))
            self.status_label.setText(f"已选择文件: {os.path.basename(file_path)}")
            
            # 如果没有设置输出文件，自动生成一个
            if not self.output_file_path:
                self.output_file_path = os.path.splitext(file_path)[0] + "_auto.xlsx"
                self.output_file_label.setText(os.path.basename(self.output_file_path))
    
    def select_output_file(self):
        file_path, _ = QFileDialog.getSaveFileName(self, "设置输出文件", "", "Excel Files (*.xlsx)")
        if file_path:
            self.output_file_path = file_path
            self.output_file_label.setText(os.path.basename(file_path))
    
    def run_analysis(self):
        # 检查是否已选择输入文件
        if not self.input_file_path:
            QMessageBox.warning(self, "警告", "请先选择要分析的Excel文件！")
            return
        
        # 检查是否已设置输出文件
        if not self.output_file_path:
            reply = QMessageBox.question(self, "确认", "未设置输出文件，将使用默认名称。是否继续？", 
                                         QMessageBox.Yes | QMessageBox.No, QMessageBox.Yes)
            if reply == QMessageBox.No:
                return
            
            # 生成默认输出文件名
            self.output_file_path = os.path.splitext(self.input_file_path)[0] + "_auto.xlsx"
            self.output_file_label.setText(os.path.basename(self.output_file_path))
        
        # 禁用按钮，防止重复点击
        self.run_btn.setEnabled(False)
        self.select_file_btn.setEnabled(False)
        self.select_output_btn.setEnabled(False)
        self.status_label.setText("分析中，请稍候...")
        
        # 创建并启动分析线程
        self.analysis_thread = AnalysisThread(self.input_file_path, self.output_file_path)
        self.analysis_thread.progress_updated.connect(self.update_progress)
        self.analysis_thread.analysis_completed.connect(self.analysis_finished)
        self.analysis_thread.start()
    
    def update_progress(self, value):
        self.progress_bar.setValue(value)
    
    def analysis_finished(self, success, message):
        # 恢复按钮状态
        self.run_btn.setEnabled(True)
        self.select_file_btn.setEnabled(True)
        self.select_output_btn.setEnabled(True)
        
        # 显示结果消息
        if success:
            QMessageBox.information(self, "成功", message)
            self.status_label.setText("分析完成")
        else:
            QMessageBox.critical(self, "错误", message)
            self.status_label.setText("分析失败")
        
        # 重置进度条
        self.progress_bar.setValue(0)


# 主函数
def main():
    app = QApplication(sys.argv)
    
    # 设置全局字体，确保中文正常显示
    font = QFont(".AppleSystemUIFont")
    app.setFont(font)
    
    window = ExcelAnalyzerApp()
    window.show()
    
    sys.exit(app.exec_())


if __name__ == "__main__":
    main()

'''
    This python program is created by David Lin
    I hope this application can help you to calculate the key index quickly that can promote your working efficiency
    By the way I wish you can respect my knowledgement which is my copyright anyway
    I will leave this company soon
    Hope you get beter and beter.
    If there will be someone want to rewrite the code of this file one day
    and in coincidence the guy have some questions about the code
    you can contact me by the E-mail
    2522808175@qq.com
'''